﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Transactions;

namespace DBAccess
{
    public class SQLServer
    {

        #region "Child Class"

        public enum mode
        {
            Windows,
            Server
        }

        public struct Param
        {
            public Param(string param, object value)
            {
                sParam = param;
                oValue = value;
            }

            public string sParam;
            public object oValue;
        }


        #endregion

        #region "Variables"

        private string strConnectionString;
        private string strLoginDBName;
        private string strLoginHost;
        private mode strLoginmode = mode.Server;
        private string strLoginPass;
        private string strLoginUser;
        private DbDataAdapter db_Adapter;
        private DbCommand db_Command;
        private static DbConnection db_Connect;
        private DbConnectionStringBuilder db_ConStr;
        private static DbProviderFactory db_Factory;
        private DbParameter db_Param;
        private static DbTransaction db_Transact;
        private System.Data.SqlClient.SqlConnection con;
        #endregion

        #region "Property"

        public string ConStr
        {
            get
            {
                return this.strConnectionString;
            }
        }

        public string Login_DBName
        {
            get
            {
                return this.strLoginDBName;
            }
            set
            {
                this.strLoginDBName = value;
            }
        }

        public string Login_Host
        {
            get
            {
                return this.strLoginHost;
            }
            set
            {
                this.strLoginHost = value;
            }
        }

        public mode Login_mode
        {
            get
            {
                return this.strLoginmode;
            }
            set
            {
                this.strLoginmode = value;
            }
        }

        public string Login_Pass
        {
            get
            {
                return this.strLoginPass;
            }
            set
            {
                this.strLoginPass = value;
            }
        }

        public string Login_User
        {
            get
            {
                return this.strLoginUser;
            }
            set
            {
                this.strLoginUser = value;
            }
        }

        public ConnectionState state
        {
            get
            {
                if (db_Connect != null)
                {
                    return db_Connect.State;
                }
                else
                {
                    return ConnectionState.Closed;
                }
            }
        }

        #endregion

        #region "Constructor"

        public SQLServer()
        {
        }

        #endregion

        #region "Public methods"

        public void BeginTransaction()
        {
            db_Transact = db_Connect.BeginTransaction();
        }

        public void Commit()
        {
            db_Transact.Commit();
        }

        public void Rollback()
        {
            db_Transact.Rollback();
        }

        public ConnectionState Connect()
        {
            db_Factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            db_Connect = db_Factory.CreateConnection();
            this.strConnectionString = this.MakeConnectionString();
            db_Connect.ConnectionString = this.strConnectionString;
            con = new System.Data.SqlClient.SqlConnection(this.MakeConnectionString());
            try
            {
                db_Connect.Open();
            }
            catch (Exception)
            {
            }

            return db_Connect.State;
        }

        public ConnectionState Connect(string strConnectionString)
        {
            db_Factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            db_Connect = db_Factory.CreateConnection();
            this.strConnectionString = strConnectionString;
            db_Connect.ConnectionString = this.strConnectionString;
            db_Connect.Open();

            return db_Connect.State;
        }

        public ConnectionState Connect(string s_Host, string s_DBName)
        {
            this.strLoginmode = mode.Windows;
            this.strLoginHost = s_Host;
            this.strLoginDBName = s_DBName;
            return this.Connect();
        }

        public ConnectionState Connect(string s_User, string s_Pass, string s_Host, string s_DBName)
        {
            this.strLoginmode = mode.Server;
            this.strLoginUser = s_User;
            this.strLoginPass = s_Pass;
            this.strLoginHost = s_Host;
            this.strLoginDBName = s_DBName;
            return this.Connect();
        }

        public ConnectionState Disconnect()
        {
            db_Connect.Close();
            return db_Connect.State;
        }

        public int NonQuery(string SqlCmd, Param[] SqlParam = null)
        {
            db_Command = MakeCommand(SqlCmd, SqlParam);
            int iCount = db_Command.ExecuteNonQuery();
            db_Command.Dispose();

            return iCount;
        }

        public DbDataReader Reader(string SqlCmd, Param[] pParam = null)
        {
            db_Command = MakeCommand(SqlCmd, pParam);
            DbDataReader dataReader = db_Command.ExecuteReader();
            db_Command.Dispose();
            return dataReader;
        }

        public DataTable GetDataTable(string SqlCmd, Param[] SqlParam = null)
        {
            DataTable dt = new DataTable();
            db_Command = MakeCommand(SqlCmd, SqlParam);
            InitializeAdapter();
            db_Adapter.SelectCommand = db_Command;
            db_Adapter.Fill(dt);
            db_Adapter.Dispose();
            db_Command.Dispose();
            return dt;
        }
        public DataTable GetDataByStoreDynamicParam(string strCommand, CommandType type,string para, string values)
        {
            try
            {
                IList<string> ListParam = !string.IsNullOrEmpty(para) ? para.Split('|') : null;
                IList<string> ListValues = values.Split('|');
                List<Param> ListSqlParameter = new List<Param>();
                if (ListParam != null && ListParam.Count > 0)
                {
                    for (int i = 0; i < ListParam.Count; i++)
                    {
                        ListSqlParameter.Add(new Param(ListParam[i], ListValues[i]));
                    }
                }

                db_Command = db_Connect.CreateCommand();
                db_Command.Transaction = db_Transact;
                db_Command.Connection = db_Connect;
                db_Command.CommandType = type;
                db_Command.CommandText = strCommand;
                db_Command.Parameters.Clear();
                if (ListSqlParameter.Count>0)
                {
                    db_Command = SetParameter(db_Command, ListSqlParameter);
                }
                DataTable dt = new DataTable();
                InitializeAdapter();
                db_Adapter.SelectCommand = db_Command;
                db_Adapter.Fill(dt);
                db_Adapter.Dispose();
                db_Command.Dispose();
                return dt;
            }
            catch
            {
                return null;
            }
        }
        public void RunSaveDataCoTransaction(DataSet ds, string strComand, string TableName)
        {

            using (TransactionScope scope = new TransactionScope())
            {
                var sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(strComand, con);
                System.Data.SqlClient.SqlCommandBuilder commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(sqlAdapter);
                sqlAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                sqlAdapter.InsertCommand = commandBuilder.GetInsertCommand();
                sqlAdapter.Update(ds, TableName);
                scope.Complete();
            }
        }
        public void RunSaveData(DataSet ds, string strComand,string TableName)
        {
            var sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(strComand, con);
            System.Data.SqlClient.SqlCommandBuilder commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(sqlAdapter);
            sqlAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
            sqlAdapter.InsertCommand = commandBuilder.GetInsertCommand();
            sqlAdapter.Update(ds, TableName);
        }
        public void RunSaveData(DataTable dt, string strComand)
        {
            var sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(strComand, con);
            System.Data.SqlClient.SqlCommandBuilder commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(sqlAdapter);
            sqlAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
            sqlAdapter.InsertCommand = commandBuilder.GetInsertCommand();
            sqlAdapter.Update(dt);
        }
        public DataTable ReturnDataTable(string strCommand, CommandType type, params Param[] Param)
        {
            try
            {
                db_Command = db_Connect.CreateCommand();
                db_Command.Transaction = db_Transact;
                db_Command.Connection = db_Connect;
                db_Command.CommandType = type;
                db_Command.CommandText = strCommand;
                db_Command.Parameters.Clear();

                if (Param != null)
                {
                    db_Command = SetParameter(db_Command, Param);
                }
                DataTable dt = new DataTable();
                InitializeAdapter();
                db_Adapter.SelectCommand = db_Command;
                db_Adapter.Fill(dt);
                db_Adapter.Dispose();
                db_Command.Dispose();
                return dt;
            }
            catch
            {
                return null;
            }
        }
        public void InitializeAdapter()
        {
            if (db_Adapter == null)
                db_Adapter = db_Factory.CreateDataAdapter();
        }

        public void AdaperSelect(string sqlCmd, List<object> Params)
        {
            db_Command = MakeCommand(sqlCmd, Params);
            db_Adapter.SelectCommand = db_Command;
        }

        public void AdaperInsert(string sqlCmd, List<object> Params)
        {
            db_Command = MakeCommand(sqlCmd, Params);
            db_Adapter.InsertCommand = db_Command;
        }

        public void AdaperUpdate(string sqlCmd, List<object> Params)
        {
            db_Command = MakeCommand(sqlCmd, Params);
            db_Adapter.UpdateCommand = db_Command;
        }

        public void AdaperDelete(string sqlCmd, List<object> Params)
        {
            db_Command = MakeCommand(sqlCmd, Params);
            db_Adapter.DeleteCommand = db_Command;
        }

        public void Fill(DataTable dt)
        {
            db_Adapter.Fill(dt);
        }

        public void Update(DataTable dt)
        {
            db_Adapter.Update(dt);
        }
        public void Update(DataSet ds)
        {
            db_Adapter.Update(ds);
        }

        #endregion

        #region "Private methods"

        private string MakeConnectionString()
        {
            string strConnectionString = "";
            this.db_ConStr = db_Factory.CreateConnectionStringBuilder();

            if (this.strLoginmode == mode.Windows)
            {
                this.db_ConStr["Integrated Security"] = true;
            }
            else
            {
                this.db_ConStr["Integrated Security"] = false;
                this.db_ConStr["User ID"] = this.strLoginUser;
                this.db_ConStr["Password"] = this.strLoginPass;
            }

            this.db_ConStr["Data Source"] = this.strLoginHost;
            this.db_ConStr["Initial Catalog"] = this.strLoginDBName;

            strConnectionString = this.db_ConStr.ConnectionString;
            this.db_ConStr.Clear();
            return strConnectionString;
        }

        private DbCommand MakeCommand(string pSqlCmd, Param[] pSqlParam = null)
        {
            db_Command = db_Connect.CreateCommand();
            db_Command.Transaction = db_Transact;
            db_Command.Connection = db_Connect;
            db_Command.CommandText = pSqlCmd;
            db_Command.Parameters.Clear();

            if (pSqlParam != null)
            {
                db_Command = SetParameter(db_Command, pSqlParam);
            }

            return db_Command;
        }

        private DbCommand MakeCommand(string pSqlCmd, List<object> pSqlParam = null)
        {
            DbCommand Command = db_Connect.CreateCommand();
            Command.Transaction = db_Transact;
            Command.Connection = db_Connect;
            Command.CommandText = pSqlCmd;
            Command.Parameters.Clear();

            if (pSqlParam != null)
            {
                foreach (object param in pSqlParam)
                {
                    Command.Parameters.Add(param);
                }
            }

            return Command;
        }

        private DbCommand SetParameter(DbCommand pCommand, Param[] pParam)
        {
            for (int i = 0; i < pParam.Length; i++)
            {
                db_Param = db_Factory.CreateParameter();
                db_Param.ParameterName = pParam[i].sParam;
                db_Param.Value = pParam[i].oValue??"";
                pCommand.Parameters.Add(db_Param);
            }

            return db_Command;
        }
        private DbCommand SetParameter(DbCommand pCommand, IList<Param> pParam)
        {
            for (int i = 0; i < pParam.Count; i++)
            {
                db_Param = db_Factory.CreateParameter();
                db_Param.ParameterName = pParam[i].sParam;
                db_Param.Value = pParam[i].oValue;
                pCommand.Parameters.Add(db_Param);
            }

            return db_Command;
        }
        /// <summary>
        /// hàm chuyền para va values động
        /// </summary>
        /// <param name="storename">GetDataVatTu1</param>
        /// <param name="para">@Dangsudung|@Mavattu|@TenVatTu</param>
        /// <param name="values">"Y|03.0001|"</param>
        /// <param name="DynamicParam"></param>
        /// <returns></returns>
        public DbCommand MakeParamDynamic(string pSqlCmd, string para, string values) //string pSqlCmd, Param[] pSqlParam = null
        {
            try
            {
                IList<string> ListParam = !string.IsNullOrEmpty(para) ? para.Split('|') : null;
                IList<string> ListValues = values.Split('|');
                List<Param> ListSqlParameter = new List<Param>();
                if (ListParam != null && ListParam.Count > 0)
                {
                    for (int i = 0; i < ListParam.Count; i++)
                    {
                        ListSqlParameter.Add(new Param(ListParam[i], ListValues[i]));
                    }
                }

                db_Command = db_Connect.CreateCommand();
                db_Command.Transaction = db_Transact;
                db_Command.Connection = db_Connect;
                db_Command.CommandText = pSqlCmd;
                db_Command.Parameters.Clear();

                if (ListSqlParameter != null)
                {
                    db_Command = SetParameter(db_Command, ListSqlParameter);
                }

                return db_Command;
            }
            catch (Exception ex) { return null; }

        }

        #endregion

    }
}
